const express = require("express");
const router = express.Router();
const path = require('path');
const fs = require('fs');
const pool = require("../pool.js");
const utils = require("../utils/index.js");
const moment = require("moment-timezone");
const multer = require("multer");
const axios = require("axios");
const CryptoJS = require("crypto-js");
const FormData = require("form-data");

const config = require("../config");
const { content } = require("googleapis/build/src/apis/content/index.js");
const apiKey = 'FdXdzDJbfkPJWRw2R2fLNgb6cnG201RS';

const util = require('util'); // 引入 util 用于 promisify
const exec = util.promisify(require('child_process').exec); // 将exec函数 Promise化



// 配置 multer 的存储设置
const storage = multer.diskStorage({
    destination: function (req, file, cb) {
        // 设置文件上传目录
        cb(null, "/home/wwwroot/default/CalorieMedia");
    },
    filename: function (req, file, cb) {
        // 生成文件名
        cb(null, Date.now() + "-" + file.originalname);
    },
});

const upload = multer({ storage: storage });

// 创建文件上传的 POST 接口
router.post("/upload", upload.single("file"), (req, res) => {
     
    const file = req.file;
    const { pk } = req.body;
    if (!file) {
        return res.status(400).send("请上传一个文件。");
    }
    let fileUrl = ''

    fileUrl = `https://fd.freevideodownload.pro/CalorieMedia/${file.filename}`;



    // 返回文件存储地址
    res.send({
        code: 0,
        message: "success",
        fileUrl: fileUrl,
    });
});
 
// 加密函数
function encrypt(message, key, iv) {
    const keyHex = CryptoJS.enc.Utf8.parse(key);
    const ivHex = CryptoJS.enc.Utf8.parse(iv);
    // 加密模式为CBC，填充模式为PKCS7
    const encrypted = CryptoJS.DES.encrypt(message, keyHex, {
        iv: ivHex,
        mode: CryptoJS.mode.CBC,
        padding: CryptoJS.pad.Pkcs7,
    });
    return encrypted.toString();
}

// 解密函数
function decrypt(ciphertext, key, iv) {
    const keyHex = CryptoJS.enc.Utf8.parse(key);
    const ivHex = CryptoJS.enc.Utf8.parse(iv); // 使用key的前8个字符作为IV
    // 解密模式为CBC，填充模式为PKCS7
    const decrypted = CryptoJS.DES.decrypt(
        {
            ciphertext: CryptoJS.enc.Base64.parse(ciphertext),
        },
        keyHex,
        {
            iv: ivHex,
            mode: CryptoJS.mode.CBC,
            padding: CryptoJS.pad.Pkcs7,
        }
    );
    return decrypted.toString(CryptoJS.enc.Utf8);
}



//foodText
router.post("/getFoodTextConfig", async (req, res) => {
    const { version } = req.body
    let sql = `SELECT * FROM Calorie_Food_Text_AI_Config WHERE version = ?`;
    let result = await mysqlQuery(sql, [version]);
    res.send({ code: 0, data: result[0] })
});
router.post("/updateFoodTextConfig", async (req, res) => {
    let { name } = await utils.getUserInfo(req, res);
    const { prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, presence_penalty_test, model_test, id } = req.body
    const sql = `
            UPDATE Calorie_Food_Text_AI_Config
            SET 
                prompt_test = ?, 
                temperature_test = ?, 
                max_tokens_test = ?, 
                top_p_test = ?, 
                frequency_penalty_test = ?, 
                presence_penalty_test = ?, 
                model_test = ?, 
                update_by = ?,
                update_date = ?
            WHERE id = ?
        `;
    let result = await mysqlQuery(sql, [prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, presence_penalty_test, model_test, name, utils.formatDate(new Date()), id])
    res.send({ code: 0, data: result[0] })
});
router.post("/updateFoodTextConfigHandel", async (req, res) => {
    let { name } = await utils.getUserInfo(req, res);
    const { prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, presence_penalty_test, model_test, id } = req.body
    const sql = `
            UPDATE Calorie_Food_Text_AI_Config
            SET 
                prompt_release = ?,
                temperature = ?,
                max_tokens = ?,
                top_p = ?,
                frequency_penalty = ?,
                presence_penalty = ?,
                model = ?,
                update_by = ?,
                update_date = ?
            WHERE id = ?
        `;
    let result = await mysqlQuery(sql, [prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, presence_penalty_test, model_test, name, utils.formatDate(new Date()), id])
    res.send({ code: 0, data: result[0] })
});
router.post('/getFoodTextVersionList', async (req, res) => {
    try {
        let sql = `SELECT version FROM Calorie_Food_Text_AI_Config`;
        let result = await mysqlQuery(sql, []);
        console.log(result)
        const formattedResult = result.map(row => ({
            id: row.version,
            name: row.version
        }));

        res.send({
            data: formattedResult
        });
    } catch (error) {
        console.error("Error fetching versions:", error);
        res.status(500).json({ error: "Internal server error" });
    }
});
router.post('/addFoodTextVersion', async (req, res) => {
    const { version } = req.body;
    try {
        // 检查表中是否已经有相同的版本
        const existingVersion = await mysqlQuery('SELECT 1 FROM Calorie_Food_Text_AI_Config WHERE version = ?', [version]);
        if (existingVersion.length > 0) {
            return res.send({ code: 1, msg: '版本已存在' });
        }
        // 获取表中最后一条记录
        const lastRecord = await mysqlQuery('SELECT * FROM Calorie_Food_Text_AI_Config ORDER BY id DESC LIMIT 1', []);

        if (lastRecord.length === 0) {
            return res.send({ code: 1, msg: '没有可复制的记录' });
        }
        // 从最后一条记录中获取值，并修改版本
        let newRecord = { ...lastRecord[0], version };

        delete newRecord.id;
        newRecord.id = utils.createId();
        // 插入新记录
        const insertQuery = 'INSERT INTO Calorie_Food_Text_AI_Config SET ?';
        await mysqlQuery(insertQuery, [newRecord]);

        res.send({ code: 0, msg: 'success' })

    } catch (error) {
        console.error(error);
        res.status(500).json({ error: '数据库操作失败，请稍后重试' });
    }
})

router.post("/ai-food-text", async (req, res) => {
    try {
        // 解密参数
        let parseData = req.body.query;
        let content = JSON.parse(decrypt(parseData, config.cryptoKey, config.cryptoIv));
        const chinaTime = new Date();
        console.log(content, 'content', chinaTime.toLocaleString());

        if (content.code !== 'OZqcJQLBUyevTh') return res.send({ code: 1, msg: 'code错误' });

        // 请求参数
        let { country, language, history, content: userContent, version, pk, uuid } = content;

        // 查AI参数
        let sql = `SELECT * FROM Calorie_Food_Text_AI_Config WHERE version = ?`;
        let params = [version || '1.0.0'];
        let result = await mysqlQuery(sql, params);
        if (result.length === 0 && version) {
            sql = `SELECT * FROM Calorie_Food_Text_AI_Config ORDER BY id DESC LIMIT 1`;
            result = await mysqlQuery(sql);
        }
        if (!result.length) return res.send({ code: 2, msg: '配置不存在' });

        let { prompt_release, temperature, max_tokens, top_p, frequency_penalty, presence_penalty, model } = result[0];

        // 替换 prompt
        let processedPrompt = prompt_release || '';
        if (country) processedPrompt = processedPrompt.replace(/\{country\}/g, country);
        if (language) processedPrompt = processedPrompt.replace(/\{language\}/g, language);

        // 解析历史
        let historyArr = [];
        let historyStr = '';
        try {
            if (typeof history === "string" && history.trim()) {
                historyArr = JSON.parse(history);
                historyStr = history; // 原始字符串存库
            } else if (Array.isArray(history)) {
                historyArr = history;
                historyStr = JSON.stringify(history);
            }
        } catch (e) {
            historyArr = [];
            historyStr = '';
        }

        // 拼 messages
        const messages = [];
        messages.push({
            role: "system",
            content: [
                { type: "text", text: processedPrompt }
            ]
        });
        for (let item of historyArr) {
            if (item.role && item.content && Array.isArray(item.content)) {
                messages.push({ role: item.role, content: item.content });
            }
        }
        if (userContent && userContent.trim()) {
            messages.push({
                role: "user",
                content: [
                    { type: "text", text: userContent }
                ]
            });
        }

        // 发 openai
        const response = await axios.post(
            'https://api.openai.com/v1/chat/completions',
            {
                model: model,
                messages,
                stream: false,
                response_format: { type: 'text' },
                temperature: Number(temperature),
                max_tokens: Number(max_tokens),
                top_p: Number(top_p),
                frequency_penalty: Number(frequency_penalty),
                presence_penalty: Number(presence_penalty)
            },
            {
                headers: {
                    'Content-Type': 'application/json',
                    'Authorization': `Bearer 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`
                }
            }
        );

        // 提取 token 等信息
        const { usage } = response.data || {};
        const { prompt_tokens = 0, completion_tokens = 0, total_tokens = 0 } = usage || {};
        const now = new Date();
        const date = utils.formatDate1(now);
        const id = utils.createId();
        let message = JSON.stringify(response.data.choices[0].message);

        // 解析 code
        function extractAndParseJson(content) {
            try {
                // 阶段1：提取核心JSON字符串
                let jsonStr = content;
                if (content.includes('```json')) {
                    jsonStr = content.split('```json')[1].split('```')[0].trim();
                } else if (content.startsWith('```')) {
                    jsonStr = content.replace(/^```/gm, '').trim();
                }
                // 阶段2：清洗
                const sanitized = jsonStr
                    .replace(/\\n/g, '')
                    .replace(/\\"/g, '"')
                    .replace(/[\u0000-\u001F]/g, '');
                // 阶段3：解析
                return JSON.parse(sanitized);
            } catch (error) {
                console.error('JSON解析失败:', error.message);
                return null;
            }
        }

        let code = '';
        try {
            const messageData = JSON.parse(message);
            const parsedData = extractAndParseJson(messageData.content);
            if (parsedData && parsedData.error_info && parsedData.error_info.error_code !== undefined) {
                code = parsedData.error_info.error_code;
            }
        } catch (e) {
            code = '';
        }

        // 存库
        const insertSql = `INSERT INTO Calorie_food_text_api_logs
  (id,uuid, date, country, language, history, content, prompt_tokens, completion_tokens, total_tokens,
    create_date, update_date, message, model, state, pk, version, code)
  VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;
        await mysqlQuery(insertSql, [
            id, uuid, date, country, language, historyStr, userContent,
            prompt_tokens, completion_tokens, total_tokens,
            now, now, message, model, '0', pk, version, code
        ]);
        // 加密返回值（如需加密，按你的 encrypt 定义，否则用 message 或原始 choices 数据）
        const encrypted = encrypt(message, config.cryptoKey, config.cryptoIv);

        res.send({
            code: 0,
            msg: 'success',
            data: encrypted
        });

    } catch (error) {
        console.error('AI Food Text error:', error?.response?.data || error.message);
        res.send({
            code: 1,
            msg: error?.response?.data || error.message,
        });
    }
});
router.post("/getFoodTextApiLog", async (req, res) => {
    let { page, size } = utils.pageSize(req.body.page, req.body.size);
    let { code, dateRange, pk, uuid } = req.body;
    let sqlConditions = [];
    const sqlParams = [];

    // code筛选
    if (code) {
        sqlConditions.push("code = ?");
        sqlParams.push(code);
    }
    if (uuid) {
        sqlConditions.push("uuid = ?");
        sqlParams.push(uuid);
    }
    // 日期范围筛选，对应create_date字段
    if (dateRange && dateRange.start && dateRange.end) {
        const startDate = moment(dateRange.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');
        sqlConditions.push("create_date BETWEEN ? AND ?");
        sqlParams.push(startDate, endDate);
    }

    // pk筛选
    if (pk) {
        sqlConditions.push("pk = ?");
        sqlParams.push(pk);
    }

    // 只查正常数据（state=0）
    sqlConditions.push("state = 0");

    let sqlBase = `SELECT * FROM Calorie_food_text_api_logs`;
    let totalBase = "FROM Calorie_food_text_api_logs";

    if (sqlConditions.length > 0) {
        sqlBase += ` WHERE ${sqlConditions.join(" AND ")}`;
        totalBase += ` WHERE ${sqlConditions.join(" AND ")}`;
    }

    let sqlCount = `SELECT COUNT(*) AS total_count ${totalBase}`;

    let { total } = await utils.getListSum(sqlCount, sqlParams, res, req);

    sqlBase += " ORDER BY create_date DESC LIMIT ? OFFSET ?";
    sqlParams.push(size, page);

    pool.query(sqlBase, sqlParams, (err, result) => {
        if (err) return res.send(utils.returnData({ code: -1, err, req }));
        result.forEach((element) => {
            // 格式化时间字段(可选)
            if (element.create_date) {
                element.create_date = moment(element.create_date)
                    .tz("Asia/Shanghai")
                    .format("YYYY-MM-DD HH:mm:ss");
            }
            if (element.update_date) {
                element.update_date = moment(element.update_date)
                    .tz("Asia/Shanghai")
                    .format("YYYY-MM-DD HH:mm:ss");
            }
        });
        res.send(
            utils.returnData({
                data: result,
                total: total,
            })
        );
    });
});

router.post("/deleteHandelFoodText", async (req, res) => {
    let { id } = req.body;
    const sql = `UPDATE Calorie_food_text_api_logs SET state = 1 WHERE id = ?`;
    let result = await mysqlQuery(sql, [id])

    res.send(
        {
            code: 0,
            msg: 'success'
        }
    );
});

router.post("/batchDeleteHandelFoodText", async (req, res) => {
    const items = req.body.items;
    for (const item of items) {
        const { id } = item;
        const sql = `UPDATE Calorie_food_text_api_logs SET state = 1 WHERE id = ?`;
        await mysqlQuery(sql, [id]);
    }
    res.send({
        code: 0,
        msg: 'success'
    });
});
//foodScan
router.post("/getFoodScanConfig", async (req, res) => {
    const { version } = req.body
    let sql = `SELECT * FROM Calorie_Food_Scan_AI_Config WHERE version = ?`;
    let result = await mysqlQuery(sql, [version]);
    res.send({ code: 0, data: result[0] })
});
router.post("/updateFoodScanConfig", async (req, res) => {
    let { name } = await utils.getUserInfo(req, res);
    const { prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, presence_penalty_test, model_test, id } = req.body
    const sql = `
            UPDATE Calorie_Food_Scan_AI_Config
            SET 
                prompt_test = ?, 
                temperature_test = ?, 
                max_tokens_test = ?, 
                top_p_test = ?, 
                frequency_penalty_test = ?, 
                presence_penalty_test = ?, 
                model_test = ?, 
                update_by = ?,
                update_date = ?
            WHERE id = ?
        `;
    let result = await mysqlQuery(sql, [prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, presence_penalty_test, model_test, name, utils.formatDate(new Date()), id])
    res.send({ code: 0, data: result[0] })
});
router.post("/updateFoodScanConfigHandel", async (req, res) => {
    let { name } = await utils.getUserInfo(req, res);
    const { prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, presence_penalty_test, model_test, id } = req.body
    const sql = `
            UPDATE Calorie_Food_Scan_AI_Config
            SET 
                prompt_release = ?,
                temperature = ?,
                max_tokens = ?,
                top_p = ?,
                frequency_penalty = ?,
                presence_penalty = ?,
                model = ?,
                update_by = ?,
                update_date = ?
            WHERE id = ?
        `;
    let result = await mysqlQuery(sql, [prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, presence_penalty_test, model_test, name, utils.formatDate(new Date()), id])
    res.send({ code: 0, data: result[0] })
});
router.post('/getFoodScanVersionList', async (req, res) => {
    try {
        let sql = `SELECT version FROM Calorie_Food_Scan_AI_Config`;
        let result = await mysqlQuery(sql, []);
        const formattedResult = result.map(row => ({
            id: row.version,
            name: row.version
        }));

        res.send({
            data: formattedResult
        });
    } catch (error) {
        console.error("Error fetching versions:", error);
        res.status(500).json({ error: "Internal server error" });
    }
});
router.post('/addFoodScanVersion', async (req, res) => {
    const { version } = req.body;
    try {
        // 检查表中是否已经有相同的版本
        const existingVersion = await mysqlQuery('SELECT 1 FROM Calorie_Food_Scan_AI_Config WHERE version = ?', [version]);
        if (existingVersion.length > 0) {
            return res.send({ code: 1, msg: '版本已存在' });
        }
        // 获取表中最后一条记录
        const lastRecord = await mysqlQuery('SELECT * FROM Calorie_Food_Scan_AI_Config ORDER BY id DESC LIMIT 1', []);

        if (lastRecord.length === 0) {
            return res.send({ code: 1, msg: '没有可复制的记录' });
        }
        // 从最后一条记录中获取值，并修改版本
        let newRecord = { ...lastRecord[0], version };

        delete newRecord.id;
        newRecord.id = utils.createId();
        // 插入新记录
        const insertQuery = 'INSERT INTO Calorie_Food_Scan_AI_Config SET ?';
        await mysqlQuery(insertQuery, [newRecord]);

        res.send({ code: 0, msg: 'success' })

    } catch (error) {
        console.error(error);
        res.status(500).json({ error: '数据库操作失败，请稍后重试' });
    }
})

router.post("/ai-food-scan", async (req, res) => {
    try {
        // 解密参数
        let parseData = req.body.query;
        let content = JSON.parse(decrypt(parseData, config.cryptoKey, config.cryptoIv));
        const chinaTime = new Date();
        console.log(content, 'content', chinaTime.toLocaleString());

        if (content.code !== 'OZqcJQLBUyevTh') return res.send({ code: 1, msg: 'code错误' });

        // 请求参数
        let { country, language, history, content: userContent, version, pk, uuid, imageUrl } = content;

        // 查AI参数
        let sql = `SELECT * FROM Calorie_Food_Scan_AI_Config WHERE version = ?`;
        let params = [version || '1.0.0'];
        let result = await mysqlQuery(sql, params);
        if (result.length === 0 && version) {
            sql = `SELECT * FROM Calorie_Food_Scan_AI_Config ORDER BY id DESC LIMIT 1`;
            result = await mysqlQuery(sql);
        }
        if (!result.length) return res.send({ code: 2, msg: '配置不存在' });

        let { prompt_release, temperature, max_tokens, top_p, frequency_penalty, presence_penalty, model } = result[0];

        // 替换 prompt
        let processedPrompt = prompt_release || '';
        if (country) processedPrompt = processedPrompt.replace(/\{country\}/g, country);
        if (language) processedPrompt = processedPrompt.replace(/\{language\}/g, language);

        // 解析历史
        let historyArr = [];
        let historyStr = '';
        try {
            if (typeof history === "string" && history.trim()) {
                historyArr = JSON.parse(history);
                historyStr = history; // 原始字符串存库
            } else if (Array.isArray(history)) {
                historyArr = history;
                historyStr = JSON.stringify(history);
            }
        } catch (e) {
            historyArr = [];
            historyStr = '';
        }

        // 拼 messages
        const messages = [];
        messages.push({
            role: "system",
            content: [
                { type: "text", text: processedPrompt }
            ]
        });
        if (imageUrl && imageUrl.trim() !== "") {
            messages.push({
                role: "user",
                content: [
                    {
                        type: "image_url",
                        image_url: {
                            url: imageUrl
                        }
                    }
                ]
            });
        }
        for (let item of historyArr) {
            if (item.role && item.content && Array.isArray(item.content)) {
                messages.push({ role: item.role, content: item.content });
            }
        }
    
        if (userContent && userContent.trim()) {
            messages.push({
                role: "user",
                content: [
                    { type: "text", text: userContent }
                ]
            });
        }

        // 发 openai
        const response = await axios.post(
            'https://api.openai.com/v1/chat/completions',
            {
                model: model,
                messages,
                stream: false,
                response_format: { type: 'text' },
                temperature: Number(temperature),
                max_tokens: Number(max_tokens),
                top_p: Number(top_p),
                frequency_penalty: Number(frequency_penalty),
                presence_penalty: Number(presence_penalty)
            },
            {
                headers: {
                    'Content-Type': 'application/json',
                    'Authorization': `Bearer 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`
                }
            }
        );

        // 提取 token 等信息
        const { usage } = response.data || {};
        const { prompt_tokens = 0, completion_tokens = 0, total_tokens = 0 } = usage || {};
        const now = new Date();
        const date = utils.formatDate1(now);
        const id = utils.createId();
        let message = JSON.stringify(response.data.choices[0].message);

        // 解析 code
        function extractAndParseJson(content) {
            try {
                // 阶段1：提取核心JSON字符串
                let jsonStr = content;
                if (content.includes('```json')) {
                    jsonStr = content.split('```json')[1].split('```')[0].trim();
                } else if (content.startsWith('```')) {
                    jsonStr = content.replace(/^```/gm, '').trim();
                }
                // 阶段2：清洗
                const sanitized = jsonStr
                    .replace(/\\n/g, '')
                    .replace(/\\"/g, '"')
                    .replace(/[\u0000-\u001F]/g, '');
                // 阶段3：解析
                return JSON.parse(sanitized);
            } catch (error) {
                console.error('JSON解析失败:', error.message);
                return null;
            }
        }

        let code = '';
        try {
            const messageData = JSON.parse(message);
            const parsedData = extractAndParseJson(messageData.content);
            if (parsedData && parsedData.error_info && parsedData.error_info.error_code !== undefined) {
                code = parsedData.error_info.error_code;
            }
        } catch (e) {
            code = '';
        }

        // 存库
        const insertSql = `INSERT INTO Calorie_food_scan_api_logs
  (id,uuid,imageUrl, date, country, language, history, content, prompt_tokens, completion_tokens, total_tokens,
    create_date, update_date, message, model, state, pk, version, code)
  VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;
        await mysqlQuery(insertSql, [
            id, uuid, imageUrl, date, country, language, historyStr, userContent,
            prompt_tokens, completion_tokens, total_tokens,
            now, now, message, model, '0', pk, version, code
        ]);
        // 加密返回值（如需加密，按你的 encrypt 定义，否则用 message 或原始 choices 数据）
        const encrypted = encrypt(message, config.cryptoKey, config.cryptoIv);

        res.send({
            code: 0,
            msg: 'success',
            data: encrypted
        });

    } catch (error) {
        console.error('AI Food Text error:', error?.response?.data || error.message);
        res.send({
            code: 1,
            msg: error?.response?.data || error.message,
        });
    }
});
router.post("/getFoodScanApiLog", async (req, res) => {
    let { page, size } = utils.pageSize(req.body.page, req.body.size);
    let { code, dateRange, pk, uuid } = req.body;
    let sqlConditions = [];
    const sqlParams = [];

    // code筛选
    if (code) {
        sqlConditions.push("code = ?");
        sqlParams.push(code);
    }
    if (uuid) {
        sqlConditions.push("uuid = ?");
        sqlParams.push(uuid);
    }
    // 日期范围筛选，对应create_date字段
    if (dateRange && dateRange.start && dateRange.end) {
        const startDate = moment(dateRange.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');
        sqlConditions.push("create_date BETWEEN ? AND ?");
        sqlParams.push(startDate, endDate);
    }

    // pk筛选
    if (pk) {
        sqlConditions.push("pk = ?");
        sqlParams.push(pk);
    }

    // 只查正常数据（state=0）
    sqlConditions.push("state = 0");

    let sqlBase = `SELECT * FROM Calorie_food_scan_api_logs`;
    let totalBase = "FROM Calorie_food_scan_api_logs";

    if (sqlConditions.length > 0) {
        sqlBase += ` WHERE ${sqlConditions.join(" AND ")}`;
        totalBase += ` WHERE ${sqlConditions.join(" AND ")}`;
    }

    let sqlCount = `SELECT COUNT(*) AS total_count ${totalBase}`;

    let { total } = await utils.getListSum(sqlCount, sqlParams, res, req);

    sqlBase += " ORDER BY create_date DESC LIMIT ? OFFSET ?";
    sqlParams.push(size, page);

    pool.query(sqlBase, sqlParams, (err, result) => {
        if (err) return res.send(utils.returnData({ code: -1, err, req }));
        result.forEach((element) => {
            // 格式化时间字段(可选)
            if (element.create_date) {
                element.create_date = moment(element.create_date)
                    .tz("Asia/Shanghai")
                    .format("YYYY-MM-DD HH:mm:ss");
            }
            if (element.update_date) {
                element.update_date = moment(element.update_date)
                    .tz("Asia/Shanghai")
                    .format("YYYY-MM-DD HH:mm:ss");
            }
        });
        res.send(
            utils.returnData({
                data: result,
                total: total,
            })
        );
    });
});

router.post("/deleteHandelFoodScan", async (req, res) => {
    let { id } = req.body;
    const sql = `UPDATE Calorie_food_scan_api_logs SET state = 1 WHERE id = ?`;
    let result = await mysqlQuery(sql, [id])

    res.send(
        {
            code: 0,
            msg: 'success'
        }
    );
});

router.post("/batchDeleteHandelFoodScan", async (req, res) => {
    const items = req.body.items;
    for (const item of items) {
        const { id } = item;
        const sql = `UPDATE Calorie_food_scan_api_logs SET state = 1 WHERE id = ?`;
        await mysqlQuery(sql, [id]);
    }
    res.send({
        code: 0,
        msg: 'success'
    });
});


//Exercise
router.post("/getExerciseConfig", async (req, res) => {
    const { version } = req.body
    let sql = `SELECT * FROM Calorie_Exercise_AI_Config WHERE version = ?`;
    let result = await mysqlQuery(sql, [version]);
    res.send({ code: 0, data: result[0] })
});
router.post("/updateExerciseConfig", async (req, res) => {
    let { name } = await utils.getUserInfo(req, res);
    const { prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, presence_penalty_test, model_test, id } = req.body
    const sql = `
            UPDATE Calorie_Exercise_AI_Config
            SET 
                prompt_test = ?, 
                temperature_test = ?, 
                max_tokens_test = ?, 
                top_p_test = ?, 
                frequency_penalty_test = ?, 
                presence_penalty_test = ?, 
                model_test = ?, 
                update_by = ?,
                update_date = ?
            WHERE id = ?
        `;
    let result = await mysqlQuery(sql, [prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, presence_penalty_test, model_test, name, utils.formatDate(new Date()), id])
    res.send({ code: 0, data: result[0] })
});
router.post("/updateExerciseConfigHandel", async (req, res) => {
    let { name } = await utils.getUserInfo(req, res);
    const { prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, presence_penalty_test, model_test, id } = req.body
    const sql = `
            UPDATE Calorie_Exercise_AI_Config
            SET 
                prompt_release = ?,
                temperature = ?,
                max_tokens = ?,
                top_p = ?,
                frequency_penalty = ?,
                presence_penalty = ?,
                model = ?,
                update_by = ?,
                update_date = ?
            WHERE id = ?
        `;
    let result = await mysqlQuery(sql, [prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, presence_penalty_test, model_test, name, utils.formatDate(new Date()), id])
    res.send({ code: 0, data: result[0] })
});
router.post('/getExerciseVersionList', async (req, res) => {
    try {
        let sql = `SELECT version FROM Calorie_Exercise_AI_Config`;
        let result = await mysqlQuery(sql, []);
        const formattedResult = result.map(row => ({
            id: row.version,
            name: row.version
        }));

        res.send({
            data: formattedResult
        });
    } catch (error) {
        console.error("Error fetching versions:", error);
        res.status(500).json({ error: "Internal server error" });
    }
});
router.post('/addExerciseVersion', async (req, res) => {
    const { version } = req.body;
    try {
        // 检查表中是否已经有相同的版本
        const existingVersion = await mysqlQuery('SELECT 1 FROM Calorie_Exercise_AI_Config WHERE version = ?', [version]);
        if (existingVersion.length > 0) {
            return res.send({ code: 1, msg: '版本已存在' });
        }
        // 获取表中最后一条记录
        const lastRecord = await mysqlQuery('SELECT * FROM Calorie_Exercise_AI_Config ORDER BY id DESC LIMIT 1', []);

        if (lastRecord.length === 0) {
            return res.send({ code: 1, msg: '没有可复制的记录' });
        }
        // 从最后一条记录中获取值，并修改版本
        let newRecord = { ...lastRecord[0], version };

        delete newRecord.id;
        newRecord.id = utils.createId();
        // 插入新记录
        const insertQuery = 'INSERT INTO Calorie_Exercise_AI_Config SET ?';
        await mysqlQuery(insertQuery, [newRecord]);

        res.send({ code: 0, msg: 'success' })

    } catch (error) {
        console.error(error);
        res.status(500).json({ error: '数据库操作失败，请稍后重试' });
    }
})

router.post("/ai-exercise", async (req, res) => {
    try {
        // 解密参数
        let parseData = req.body.query;
        let content = JSON.parse(decrypt(parseData, config.cryptoKey, config.cryptoIv));
        const chinaTime = new Date();
        console.log(content, 'content', chinaTime.toLocaleString());

        if (content.code !== 'OZqcJQLBUyevTh') return res.send({ code: 1, msg: 'code错误' });

        // 请求参数
        let { language, content: userContent, version, pk, uuid } = content;

        // 查AI参数
        let sql = `SELECT * FROM Calorie_Exercise_AI_Config WHERE version = ?`;
        let params = [version || '1.0.0'];
        let result = await mysqlQuery(sql, params);
        if (result.length === 0 && version) {
            sql = `SELECT * FROM Calorie_Exercise_AI_Config ORDER BY id DESC LIMIT 1`;
            result = await mysqlQuery(sql);
        }
        if (!result.length) return res.send({ code: 2, msg: '配置不存在' });

        let { prompt_release, temperature, max_tokens, top_p, frequency_penalty, presence_penalty, model } = result[0];

        // 替换 prompt
        let processedPrompt = prompt_release || '';
        if (language) processedPrompt = processedPrompt.replace(/\{language\}/g, language);



        // 拼 messages
        const messages = [];
        messages.push({
            role: "system",
            content: [
                { type: "text", text: processedPrompt }
            ]
        });

        if (userContent && userContent.trim()) {
            messages.push({
                role: "user",
                content: [
                    { type: "text", text: userContent }
                ]
            });
        }

        // 发 openai
        const response = await axios.post(
            'https://api.openai.com/v1/chat/completions',
            {
                model: model,
                messages,
                stream: false,
                response_format: { type: 'text' },
                temperature: Number(temperature),
                max_tokens: Number(max_tokens),
                top_p: Number(top_p),
                frequency_penalty: Number(frequency_penalty),
                presence_penalty: Number(presence_penalty)
            },
            {
                headers: {
                    'Content-Type': 'application/json',
                    'Authorization': `Bearer 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`
                }
            }
        );

        // 提取 token 等信息
        const { usage } = response.data || {};
        const { prompt_tokens = 0, completion_tokens = 0, total_tokens = 0 } = usage || {};
        const now = new Date();
        const date = utils.formatDate1(now);
        const id = utils.createId();
        let message = JSON.stringify(response.data.choices[0].message);

        // 解析 code
        function extractAndParseJson(content) {
            try {
                // 阶段1：提取核心JSON字符串
                let jsonStr = content;
                if (content.includes('```json')) {
                    jsonStr = content.split('```json')[1].split('```')[0].trim();
                } else if (content.startsWith('```')) {
                    jsonStr = content.replace(/^```/gm, '').trim();
                }
                // 阶段2：清洗
                const sanitized = jsonStr
                    .replace(/\\n/g, '')
                    .replace(/\\"/g, '"')
                    .replace(/[\u0000-\u001F]/g, '');
                // 阶段3：解析
                return JSON.parse(sanitized);
            } catch (error) {
                console.error('JSON解析失败:', error.message);
                return null;
            }
        }

        let code = '';
        try {
            const messageData = JSON.parse(message);
            const parsedData = extractAndParseJson(messageData.content);
            if (parsedData && parsedData.error_info && parsedData.error_info.error_code !== undefined) {
                code = parsedData.error_info.error_code;
            }
        } catch (e) {
            code = '';
        }

        // 存库
        const insertSql = `INSERT INTO Calorie_exercise_api_logs
  (id,uuid, date,  language,  content, prompt_tokens, completion_tokens, total_tokens,
    create_date, update_date, message, model, state, pk, version, code)
  VALUES (?, ?,   ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;
        await mysqlQuery(insertSql, [
            id, uuid, date, language, userContent,
            prompt_tokens, completion_tokens, total_tokens,
            now, now, message, model, '0', pk, version, code
        ]);
        // 加密返回值（如需加密，按你的 encrypt 定义，否则用 message 或原始 choices 数据）
        const encrypted = encrypt(message, config.cryptoKey, config.cryptoIv);

        res.send({
            code: 0,
            msg: 'success',
            data: encrypted
        });

    } catch (error) {
        console.error('AI Food Text error:', error?.response?.data || error.message);
        res.send({
            code: 1,
            msg: error?.response?.data || error.message,
        });
    }
});
router.post("/getExerciseApiLog", async (req, res) => {
    let { page, size } = utils.pageSize(req.body.page, req.body.size);
    let { code, dateRange, pk, uuid } = req.body;
    let sqlConditions = [];
    const sqlParams = [];

    // code筛选
    if (code) {
        sqlConditions.push("code = ?");
        sqlParams.push(code);
    }
    if (uuid) {
        sqlConditions.push("uuid = ?");
        sqlParams.push(uuid);
    }
    // 日期范围筛选，对应create_date字段
    if (dateRange && dateRange.start && dateRange.end) {
        const startDate = moment(dateRange.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');
        sqlConditions.push("create_date BETWEEN ? AND ?");
        sqlParams.push(startDate, endDate);
    }

    // pk筛选
    if (pk) {
        sqlConditions.push("pk = ?");
        sqlParams.push(pk);
    }

    // 只查正常数据（state=0）
    sqlConditions.push("state = 0");

    let sqlBase = `SELECT * FROM Calorie_exercise_api_logs`;
    let totalBase = "FROM Calorie_exercise_api_logs";

    if (sqlConditions.length > 0) {
        sqlBase += ` WHERE ${sqlConditions.join(" AND ")}`;
        totalBase += ` WHERE ${sqlConditions.join(" AND ")}`;
    }

    let sqlCount = `SELECT COUNT(*) AS total_count ${totalBase}`;

    let { total } = await utils.getListSum(sqlCount, sqlParams, res, req);

    sqlBase += " ORDER BY create_date DESC LIMIT ? OFFSET ?";
    sqlParams.push(size, page);

    pool.query(sqlBase, sqlParams, (err, result) => {
        if (err) return res.send(utils.returnData({ code: -1, err, req }));
        result.forEach((element) => {
            // 格式化时间字段(可选)
            if (element.create_date) {
                element.create_date = moment(element.create_date)
                    .tz("Asia/Shanghai")
                    .format("YYYY-MM-DD HH:mm:ss");
            }
            if (element.update_date) {
                element.update_date = moment(element.update_date)
                    .tz("Asia/Shanghai")
                    .format("YYYY-MM-DD HH:mm:ss");
            }
        });
        res.send(
            utils.returnData({
                data: result,
                total: total,
            })
        );
    });
});

router.post("/deleteHandelExercise", async (req, res) => {
    let { id } = req.body;
    const sql = `UPDATE Calorie_exercise_api_logs SET state = 1 WHERE id = ?`;
    let result = await mysqlQuery(sql, [id])

    res.send(
        {
            code: 0,
            msg: 'success'
        }
    );
});

router.post("/batchDeleteHandelExercise", async (req, res) => {
    const items = req.body.items;
    for (const item of items) {
        const { id } = item;
        const sql = `UPDATE Calorie_exercise_api_logs SET state = 1 WHERE id = ?`;
        await mysqlQuery(sql, [id]);
    }
    res.send({
        code: 0,
        msg: 'success'
    });
});










router.post("/getUserList", async (req, res) => {
    let { page, size } = utils.pageSize(req.body.page, req.body.size);
    let { uuid, dateRange } = req.body;
    let sqlConditions = [];

    const sqlParams = [];
    if (uuid) {
        sqlConditions.push("uuid = ?");
        sqlParams.push(uuid);
    }
    if (dateRange && dateRange.start && dateRange.end) {
        const startDate = moment(dateRange.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');

        sqlConditions.push("update_date BETWEEN ? AND ?");
        sqlParams.push(startDate, endDate);
    }


    let sqlBase = `SELECT * FROM ainote_user_list`;
    let totalBase = "FROM ainote_user_list";

    if (sqlConditions.length > 0) {
        sqlBase += ` WHERE ${sqlConditions.join(" AND ")}`;
        totalBase += ` WHERE ${sqlConditions.join(" AND ")}`;
    }

    let sqlCount = `SELECT COUNT(*) AS total_count ${totalBase}`;

    let { total } = await utils.getListSum(sqlCount, sqlParams, res, req);

    sqlBase += " ORDER BY update_date DESC LIMIT ? OFFSET ?";
    sqlParams.push(size, page);
    await pool.query(sqlBase, sqlParams, (err, result) => {
        if (err) return res.send(utils.returnData({ code: -1, err, req }));
        result.forEach((element) => {

            element.update_date = moment(element.update_date)
                .tz("Asia/Shanghai")
                .format("YYYY-MM-DD HH:mm:ss");

        });
        res.send(
            utils.returnData({
                data: result,
                total: total,
            })
        );
    });
});
router.post("/getChatList", async (req, res) => {
    const { uuid } = req.body;

    const sqlParams = [];
    let sqlConditions = []; // 初始化SQL条件数组

    if (uuid) {
        sqlConditions.push("uuid = ?");
        sqlParams.push(uuid);
    }

    let sqlBase = `SELECT * FROM ainote_aichat_list`;
    let totalBase = "FROM ainote_aichat_list";

    // 如果有额外的条件，添加到基础SQL中
    if (sqlConditions.length > 0) {
        const whereClause = sqlConditions.join(' AND ');
        sqlBase += ` WHERE ${whereClause}`;
        totalBase += ` WHERE ${whereClause}`;
    }

    let sqlCount = `SELECT COUNT(*) AS total_count ${totalBase}`;

    //获取总数
    let { total } = await utils.getListSum(sqlCount, sqlParams, res, req);

    // 排序但不分页
    sqlBase += " ORDER BY eventTimeMillis ASC";

    // 执行查询
    await pool.query(sqlBase, sqlParams, (err, result) => {
        if (err) return res.send(utils.returnData({ code: -1, err, req }));
        result.forEach((element) => {
            element.update_date = moment(element.update_date)
                .tz("Asia/Shanghai")
                .format("YYYY-MM-DD HH:mm:ss");
        });
        res.send(
            utils.returnData({
                data: result,
                total: total,
            })
        );
    });
});
router.post('/storeChatMessages', async (req, res) => {
    const { uuid, content, role, eventTimeMillis } = req.body;
    let contents = {};
    contents.id = utils.createId();
    contents.uuid = uuid;
    contents.content = content;
    contents.role = role;
    contents.eventTimeMillis = eventTimeMillis;
    contents.update_date = utils.formatDate(new Date());

    try {
        // 检查uuid是否存在于ainote_user_list表中
        const checkSql = 'SELECT COUNT(*) AS count FROM ainote_user_list WHERE uuid = ?';
        const checkResult = await mysqlQuery(checkSql, [uuid]);

        if (checkResult[0].count === 0) {
            // 如果没有重复，插入新的uuid
            const insertUserSql = 'INSERT INTO ainote_user_list (id, uuid) VALUES (?, ?)';
            await mysqlQuery(insertUserSql, [utils.createId(), uuid]);
        } else {
            // 如果有重复，更新现有记录的update_date
            const updateUserSql = 'UPDATE ainote_user_list SET update_date = ? WHERE uuid = ?';
            await mysqlQuery(updateUserSql, [utils.formatDate(new Date()), uuid]);
        }

        // 插入聊天记录到ainote_user_list
        const insertChatSql = `
            INSERT INTO ainote_user_list (id, uuid, content, role, eventTimeMillis, update_date)
            VALUES (?, ?, ?, ?, ?,?)
        `;
        const insertChatResult = await mysqlQuery(insertChatSql, [utils.createId(), uuid, content, role, eventTimeMillis, utils.formatDate(new Date())]);

        if (insertChatResult.affectedRows === 1) {
            res.send({ code: 0, msg: "success" });
        } else {
            res.status(500).send({ code: 1, msg: "Failed to insert chat message" });
        }
    } catch (error) {
        console.error('Database Error:', error);
        res.status(500).send({ code: 1, msg: "Database error" });
    }
});


//表单1
router.post('/reportSearchText', async (req, res) => {
    const { content, language, country,pk } = req.body;
    // 参数校验
    if (!content || !language) {
        return res.status(400).json({ code: 400, msg: "content 和 language 必填" });
    }

    const id = utils.createId(); // 用你的工具生成唯一id

    const sql = `
        INSERT INTO Calorie_Form1 (id, content, language, country,pk)
        VALUES (?, ?, ?, ?, ?)
    `;

    try {
        await mysqlQuery(sql, [id, content, language, country,pk]);
        res.send({ code: 0, msg: "success" });
    } catch (err) {
        res.status(500).json({ code: 500, msg: "数据库错误", error: err.message });
    }
});


//表单2
router.post('/reportSearchTextEmpty', async (req, res) => {
    const { content, language, country,pk } = req.body;
    // 参数校验
    if (!content || !language) {
        return res.status(400).json({ code: 400, msg: "content 和 language 必填" });
    }

    const id = utils.createId(); // 用你的工具生成唯一id

    const sql = `
        INSERT INTO Calorie_Form2 (id, content, language, country,pk)
        VALUES (?, ?, ?, ?, ?)
    `;

    try {
        await mysqlQuery(sql, [id, content, language, country,pk]);
        res.send({ code: 0, msg: "success" });
    } catch (err) {
        res.status(500).json({ code: 500, msg: "数据库错误", error: err.message });
    }
})

router.post('/reportUserRecord', async (req, res) => {
    const { type, name, url, source, language, country,pk } = req.body;

     

    const id = utils.createId();

    const sql = `
        INSERT INTO Calorie_Form3 
        (id, type, name, url, source, language, country,pk)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    `;

    try {
        await mysqlQuery(sql, [id, type, name, url, source, language, country,pk]);
        res.send({ code: 0, msg: "success" });
    } catch (err) {
        res.status(500).json({ code: 500, msg: "数据库错误", error: err.message });
    }
});
router.post('/reportUserRecordFail', async (req, res) => {
    const { type, name, url, source, language, country,code,pk } = req.body;

     

    const id = utils.createId();

    const sql = `
        INSERT INTO Calorie_Form4
        (id, type, name, url, source, language, country,code,pk)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    `;

    try {
        await mysqlQuery(sql, [id, type, name, url, source, language, country,code,pk]);
        res.send({ code: 0, msg: "success" });
    } catch (err) {
        res.status(500).json({ code: 500, msg: "数据库错误", error: err.message });
    }
});

//表单1列表
router.post('/getReportSearchTextList', async (req, res) => {
    let { page, size } = utils.pageSize(req.body.page, req.body.size);
    let { content, language, country, dateRange } = req.body;
    let sqlConditions = [];
    const sqlParams = [];

    if (content) {
        sqlConditions.push("content LIKE ?");
        sqlParams.push(`%${content}%`);
    }
    if (language) {
        sqlConditions.push("language = ?");
        sqlParams.push(language);
    }
    if (country) {
        sqlConditions.push("country = ?");
        sqlParams.push(country);
    }
    if (dateRange && dateRange.start && dateRange.end) {
        const startDate = moment(dateRange.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');
        sqlConditions.push("created_at BETWEEN ? AND ?");
        sqlParams.push(startDate, endDate);
    }
    sqlConditions.push("state = 0");
    let sqlBase = "SELECT * FROM Calorie_Form1";
    let totalBase = "FROM Calorie_Form1";
    if (sqlConditions.length) {
        sqlBase += " WHERE " + sqlConditions.join(" AND ");
        totalBase += " WHERE " + sqlConditions.join(" AND ");
    }

    let sqlCount = `SELECT COUNT(*) AS total ${totalBase}`;
    let { total } = await utils.getListSum(sqlCount, sqlParams, res, req);

    sqlBase += " ORDER BY created_at DESC LIMIT ? OFFSET ?";
    sqlParams.push(size, page);

    pool.query(sqlBase, sqlParams, (err, result) => {
        if (err) return res.send(utils.returnData({ code: -1, err, req }));
        result.forEach(item => {
            if (item.created_at) {
                item.created_at = moment(item.created_at)
                    .tz("Asia/Shanghai")
                    .format("YYYY-MM-DD HH:mm:ss");
            }
        });
        res.send(
            utils.returnData({
                data: result,
                total: total,
            })
        );
    });
});

router.post("/deleteHandelSearchText", async (req, res) => {
    let { id } = req.body;
    const sql = `UPDATE Calorie_Form1 SET state = 1 WHERE id = ?`;
    let result = await mysqlQuery(sql, [id])

    res.send(
        {
            code: 0,
            msg: 'success'
        }
    );
});

router.post("/batchDeleteHandelSearchText", async (req, res) => {
    const items = req.body.items;
    for (const item of items) {
        const { id } = item;
        const sql = `UPDATE Calorie_Form1 SET state = 1 WHERE id = ?`;
        await mysqlQuery(sql, [id]);
    }
    res.send({
        code: 0,
        msg: 'success'
    });
});
router.post('/getCalorieForm1LanguageList', async (req, res) => {
    const sql = "SELECT DISTINCT language FROM Calorie_Form1 WHERE language IS NOT NULL AND language != ''";
    try {
        const rows = await mysqlQuery(sql);
        const list = rows.map(row => ({
            id: row.language,
            name: row.language
        }));
        res.send({ code: 0, data: list, message: 'success' });
    } catch (err) {
        res.status(500).json({ code: 500, message: "数据库错误", error: err.message });
    }
});
router.post('/getCalorieForm1CountryList', async (req, res) => {
    const sql = "SELECT DISTINCT country FROM Calorie_Form1 WHERE country IS NOT NULL AND country != ''";
    try {
        const rows = await mysqlQuery(sql);
        const list = rows.map(row => ({
            id: row.country,
            name: row.country
        }));
        res.send({ code: 0, data: list, message: 'success' });
    } catch (err) {
        res.status(500).json({ code: 500, message: "数据库错误", error: err.message });
    }
});
//表单2列表
router.post('/getReportSearchTextEmptyList', async (req, res) => {
    let { page, size } = utils.pageSize(req.body.page, req.body.size);
    let { content, language, country, dateRange } = req.body;
    let sqlConditions = [];
    const sqlParams = [];

    if (content) {
        sqlConditions.push("content LIKE ?");
        sqlParams.push(`%${content}%`);
    }
    if (language) {
        sqlConditions.push("language = ?");
        sqlParams.push(language);
    }
    if (country) {
        sqlConditions.push("country = ?");
        sqlParams.push(country);
    }
    if (dateRange && dateRange.start && dateRange.end) {
        const startDate = moment(dateRange.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');
        sqlConditions.push("created_at BETWEEN ? AND ?");
        sqlParams.push(startDate, endDate);
    }
    sqlConditions.push("state = 0");
    let sqlBase = "SELECT * FROM Calorie_Form2";
    let totalBase = "FROM Calorie_Form2";
    if (sqlConditions.length) {
        sqlBase += " WHERE " + sqlConditions.join(" AND ");
        totalBase += " WHERE " + sqlConditions.join(" AND ");
    }

    let sqlCount = `SELECT COUNT(*) AS total ${totalBase}`;
    let { total } = await utils.getListSum(sqlCount, sqlParams, res, req);

    sqlBase += " ORDER BY created_at DESC LIMIT ? OFFSET ?";
    sqlParams.push(size, page);

    pool.query(sqlBase, sqlParams, (err, result) => {
        if (err) return res.send(utils.returnData({ code: -1, err, req }));
        result.forEach(item => {
            if (item.created_at) {
                item.created_at = moment(item.created_at)
                    .tz("Asia/Shanghai")
                    .format("YYYY-MM-DD HH:mm:ss");
            }
        });
        res.send(
            utils.returnData({
                data: result,
                total: total,
            })
        );
    });
});
router.post("/deleteHandelSearchTextEmpty", async (req, res) => {
    let { id } = req.body;
    const sql = `UPDATE Calorie_Form2 SET state = 1 WHERE id = ?`;
    let result = await mysqlQuery(sql, [id])

    res.send(
        {
            code: 0,
            msg: 'success'
        }
    );
});

router.post("/batchDeleteHandelSearchTextEmpty", async (req, res) => {
    const items = req.body.items;
    for (const item of items) {
        const { id } = item;
        const sql = `UPDATE Calorie_Form2 SET state = 1 WHERE id = ?`;
        await mysqlQuery(sql, [id]);
    }
    res.send({
        code: 0,
        msg: 'success'
    });
});
router.post('/getCalorieForm2LanguageList', async (req, res) => {
    const sql = "SELECT DISTINCT language FROM Calorie_Form2 WHERE language IS NOT NULL AND language != ''";
    try {
        const rows = await mysqlQuery(sql);
        const list = rows.map(row => ({
            id: row.language,
            name: row.language
        }));
        res.send({ code: 0, data: list, message: 'success' });
    } catch (err) {
        res.status(500).json({ code: 500, message: "数据库错误", error: err.message });
    }
});
router.post('/getCalorieForm2CountryList', async (req, res) => {
    const sql = "SELECT DISTINCT country FROM Calorie_Form2 WHERE country IS NOT NULL AND country != ''";
    try {
        const rows = await mysqlQuery(sql);
        const list = rows.map(row => ({
            id: row.country,
            name: row.country
        }));
        res.send({ code: 0, data: list, message: 'success' });
    } catch (err) {
        res.status(500).json({ code: 500, message: "数据库错误", error: err.message });
    }
});
//表单3列表
router.post('/getUserRecordList', async (req, res) => {
    let { page, size } = utils.pageSize(req.body.page, req.body.size);
    let { type, name, language, country, source, dateRange } = req.body;
    let sqlConditions = [];
    const sqlParams = [];

    if (type) {
        sqlConditions.push("type = ?");
        sqlParams.push(type);
    }
    if (source) {
        sqlConditions.push("source = ?");
        sqlParams.push(source);
    }
    if (name) {
        sqlConditions.push("name LIKE ?");
        sqlParams.push(`%${name}%`);
    }
    if (language) {
        sqlConditions.push("language = ?");
        sqlParams.push(language);
    }
    if (country) {
        sqlConditions.push("country = ?");
        sqlParams.push(country);
    }
    if (dateRange && dateRange.start && dateRange.end) {
        const startDate = moment(dateRange.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');
        sqlConditions.push("created_at BETWEEN ? AND ?");
        sqlParams.push(startDate, endDate);
    }
    sqlConditions.push("state = 0");
    let sqlBase = "SELECT * FROM Calorie_Form3";
    let totalBase = "FROM Calorie_Form3";
    if (sqlConditions.length) {
        sqlBase += " WHERE " + sqlConditions.join(" AND ");
        totalBase += " WHERE " + sqlConditions.join(" AND ");
    }

    let sqlCount = `SELECT COUNT(*) AS total ${totalBase}`;
    let { total } = await utils.getListSum(sqlCount, sqlParams, res, req);

    sqlBase += " ORDER BY created_at DESC LIMIT ? OFFSET ?";
    sqlParams.push(size, page);

    pool.query(sqlBase, sqlParams, (err, result) => {
        if (err) return res.send(utils.returnData({ code: -1, err, req }));
        result.forEach(item => {
            if (item.created_at) {
                item.created_at = moment(item.created_at)
                    .tz("Asia/Shanghai")
                    .format("YYYY-MM-DD HH:mm:ss");
            }
        });
        res.send(
            utils.returnData({
                data: result,
                total: total,
            })
        );
    });
});
router.post("/deleteHandelRecord", async (req, res) => {
    let { id } = req.body;
    const sql = `UPDATE Calorie_Form3 SET state = 1 WHERE id = ?`;
    let result = await mysqlQuery(sql, [id])

    res.send(
        {
            code: 0,
            msg: 'success'
        }
    );
});

router.post("/batchDeleteHandelRecord", async (req, res) => {
    const items = req.body.items;
    for (const item of items) {
        const { id } = item;
        const sql = `UPDATE Calorie_Form3 SET state = 1 WHERE id = ?`;
        await mysqlQuery(sql, [id]);
    }
    res.send({
        code: 0,
        msg: 'success'
    });
});
router.post('/getCalorieForm3SourceList', async (req, res) => {
    const sql = "SELECT DISTINCT source FROM Calorie_Form3 WHERE source IS NOT NULL AND source != ''";
    try {
        const rows = await mysqlQuery(sql);
        const list = rows.map(row => ({
            id: row.source,
            name: row.source
        }));
        res.send({ code: 0, data: list, message: 'success' });
    } catch (err) {
        res.status(500).json({ code: 500, message: "数据库错误", error: err.message });
    }
});
router.post('/getCalorieForm3LanguageList', async (req, res) => {
    const sql = "SELECT DISTINCT language FROM Calorie_Form3 WHERE language IS NOT NULL AND language != ''";
    try {
        const rows = await mysqlQuery(sql);
        const list = rows.map(row => ({
            id: row.language,
            name: row.language
        }));
        res.send({ code: 0, data: list, message: 'success' });
    } catch (err) {
        res.status(500).json({ code: 500, message: "数据库错误", error: err.message });
    }
});
router.post('/getCalorieForm3CountryList', async (req, res) => {
    const sql = "SELECT DISTINCT country FROM Calorie_Form3 WHERE country IS NOT NULL AND country != ''";
    try {
        const rows = await mysqlQuery(sql);
        const list = rows.map(row => ({
            id: row.country,
            name: row.country
        }));
        res.send({ code: 0, data: list, message: 'success' });
    } catch (err) {
        res.status(500).json({ code: 500, message: "数据库错误", error: err.message });
    }
});
//表单4列表
router.post('/getUserRecordFailList', async (req, res) => {
    let { page, size } = utils.pageSize(req.body.page, req.body.size);
    let { type, name, language, country, dateRange, code, source } = req.body;
    let sqlConditions = [];
    const sqlParams = [];

    if (type) {
        sqlConditions.push("type = ?");
        sqlParams.push(type);
    }
    if (code) {
        sqlConditions.push("code = ?");
        sqlParams.push(code);
    }
    if (source) {
        sqlConditions.push("source = ?");
        sqlParams.push(source);
    }
    if (name) {
        sqlConditions.push("name LIKE ?");
        sqlParams.push(`%${name}%`);
    }
    if (language) {
        sqlConditions.push("language = ?");
        sqlParams.push(language);
    }
    if (country) {
        sqlConditions.push("country = ?");
        sqlParams.push(country);
    }
    if (dateRange && dateRange.start && dateRange.end) {
        const startDate = moment(dateRange.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');
        sqlConditions.push("created_at BETWEEN ? AND ?");
        sqlParams.push(startDate, endDate);
    }

    sqlConditions.push("state = 0");
    let sqlBase = "SELECT * FROM Calorie_Form4";
    let totalBase = "FROM Calorie_Form4";
    if (sqlConditions.length) {
        sqlBase += " WHERE " + sqlConditions.join(" AND ");
        totalBase += " WHERE " + sqlConditions.join(" AND ");
    }

    let sqlCount = `SELECT COUNT(*) AS total ${totalBase}`;
    let { total } = await utils.getListSum(sqlCount, sqlParams, res, req);

    sqlBase += " ORDER BY created_at DESC LIMIT ? OFFSET ?";
    sqlParams.push(size, page);

    pool.query(sqlBase, sqlParams, (err, result) => {
        if (err) return res.send(utils.returnData({ code: -1, err, req }));
        result.forEach(item => {
            if (item.created_at) {
                item.created_at = moment(item.created_at)
                    .tz("Asia/Shanghai")
                    .format("YYYY-MM-DD HH:mm:ss");
            }
        });
        res.send(
            utils.returnData({
                data: result,
                total: total,
            })
        );
    });
});
router.post("/deleteHandelRecordFail", async (req, res) => {
    let { id } = req.body;
    const sql = `UPDATE Calorie_Form4 SET state = 1 WHERE id = ?`;
    let result = await mysqlQuery(sql, [id])

    res.send(
        {
            code: 0,
            msg: 'success'
        }
    );
});

router.post("/batchDeleteHandelRecordFail", async (req, res) => {
    const items = req.body.items;
    for (const item of items) {
        const { id } = item;
        const sql = `UPDATE Calorie_Form4 SET state = 1 WHERE id = ?`;
        await mysqlQuery(sql, [id]);
    }
    res.send({
        code: 0,
        msg: 'success'
    });
});
router.post('/getCalorieForm4SourceList', async (req, res) => {
    const sql = "SELECT DISTINCT source FROM Calorie_Form4 WHERE source IS NOT NULL AND source != ''";
    try {
        const rows = await mysqlQuery(sql);
        const list = rows.map(row => ({
            id: row.source,
            name: row.source
        }));
        res.send({ code: 0, data: list, message: 'success' });
    } catch (err) {
        res.status(500).json({ code: 500, message: "数据库错误", error: err.message });
    }
});
router.post('/getCalorieForm4LanguageList', async (req, res) => {
    const sql = "SELECT DISTINCT language FROM Calorie_Form4 WHERE language IS NOT NULL AND language != ''";
    try {
        const rows = await mysqlQuery(sql);
        const list = rows.map(row => ({
            id: row.language,
            name: row.language
        }));
        res.send({ code: 0, data: list, message: 'success' });
    } catch (err) {
        res.status(500).json({ code: 500, message: "数据库错误", error: err.message });
    }
});
router.post('/getCalorieForm4CountryList', async (req, res) => {
    const sql = "SELECT DISTINCT country FROM Calorie_Form4 WHERE country IS NOT NULL AND country != ''";
    try {
        const rows = await mysqlQuery(sql);
        const list = rows.map(row => ({
            id: row.country,
            name: row.country
        }));
        res.send({ code: 0, data: list, message: 'success' });
    } catch (err) {
        res.status(500).json({ code: 500, message: "数据库错误", error: err.message });
    }
});
router.post('/getCalorieForm4CodeList', async (req, res) => {
    const sql = "SELECT DISTINCT code FROM Calorie_Form4 WHERE code IS NOT NULL AND code != ''";
    try {
        const rows = await mysqlQuery(sql);
        const list = rows.map(row => ({
            id: row.code,
            name: row.code
        }));
        res.send({ code: 0, data: list, message: 'success' });
    } catch (err) {
        res.status(500).json({ code: 500, message: "数据库错误", error: err.message });
    }
});
router.post('/getTokens', async (req, res) => {
    const { dateRange } = req.body;
    let sql = `SELECT * FROM Calorie_token_usage_per_day WHERE 1=1`;
    let params = [];

    if (dateRange && dateRange.start && dateRange.end) {
        const startDate = moment(dateRange.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');

        sql += ` AND date BETWEEN ? AND ?`;
        params.push(startDate, endDate);
    }

    try {
        const results = await mysqlQuery(sql, params);

        const aggregatedData = {};

        results.forEach(row => {
            const {
                date, prompt_tokens, completion_tokens, total_tokens,
                prompt_dollar, completion_dollar, total_dollar, model, update_date
            } = row;

            if (!aggregatedData[date]) {
                aggregatedData[date] = {
                    date,
                    prompt_tokens: 0,
                    completion_tokens: 0,
                    total_tokens: 0,
                    prompt_dollar: 0,
                    completion_dollar: 0,
                    total_dollar: 0,
                    update_date: update_date,
                    model: model,
                };
            }

            aggregatedData[date].prompt_tokens += prompt_tokens;
            aggregatedData[date].completion_tokens += completion_tokens;
            aggregatedData[date].total_tokens += total_tokens;

            // 使用四舍五入并转换为数字
            aggregatedData[date].prompt_dollar = parseFloat((aggregatedData[date].prompt_dollar + prompt_dollar).toFixed(4));
            aggregatedData[date].completion_dollar = parseFloat((aggregatedData[date].completion_dollar + completion_dollar).toFixed(4));
            aggregatedData[date].total_dollar = parseFloat((aggregatedData[date].total_dollar + total_dollar).toFixed(4));
        });

        const aggregatedArray = Object.values(aggregatedData);
        const convertedResults = aggregatedArray.map(result => {
            const convertedResult = { ...result };

            if (convertedResult.date) {
                convertedResult.date = convertToChinaTime(convertedResult.date);
            }

            if (convertedResult.update_date) {
                convertedResult.update_date = convertToChinaTime(convertedResult.update_date);
            }

            return convertedResult;
        });

        res.send(
            utils.returnData({
                data: convertedResults
            })
        );
    } catch (error) {
        console.error('Error fetching token usage data:', error);
        res.status(500).send('Internal Server Error');
    }
});
// 时间转换函数，只保留 "月-日" 部分
function convertToChinaTime(utcDateStr) {
    const utcDate = new Date(utcDateStr);
    const chinaDate = new Date(utcDate.getTime() + 8 * 60 * 60 * 1000); // 添加8小时
    const year = chinaDate.getUTCFullYear(); // 提取年份，后续比较
    const month = String(chinaDate.getUTCMonth() + 1).padStart(2, '0'); // 月份从0开始，需要加1
    const day = String(chinaDate.getUTCDate()).padStart(2, '0'); // 日期
    return `${month}-${day}`;
}
router.post('/getFaceTokens', async (req, res) => {
    const { dateRange } = req.body;
    let sql = `SELECT * FROM Calorie_token_usage_per_day`;
    let params = [];

    if (dateRange && dateRange.start && dateRange.end) {
        const startDate = moment(dateRange.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');

        sql += ` WHERE date BETWEEN ? AND ?`;
        params.push(startDate, endDate);
    }
    try {
        const results = await mysqlQuery(sql, params);

        // 遍历 results，转换 date 和 update_date 字段
        const convertedResults = results.map(result => {
            const convertedResult = { ...result };

            if (convertedResult.date) {
                convertedResult.date = convertToChinaTime(convertedResult.date);
            }

            if (convertedResult.update_date) {
                convertedResult.update_date = convertToChinaTime(convertedResult.update_date);
            }

            return convertedResult;
        });

        res.send(
            utils.returnData({
                data: convertedResults
            })
        );
    } catch (error) {
        res.status(500).json({
            success: false,
            message: 'Internal Server Error'
        });
    }
})
async function mysqlQuery(sql, arr) {

    return new Promise((resolve, reject) => {
        pool.query(sql, arr, (error, results, fields) => {

            if (error) {
                console.log(error)
                return reject(error);
            }
            resolve(results);
        });
    });
}
module.exports = router; 